import os
import string
from app import useDB, log
from app.db import test_case_manage, test_suite_manage
import xlwt
import pandas as pd
import time
from pathlib import Path

def export_project_result(idaa):
    conditionList = ['test_suite_id']
    valueList = []
    valueList.append(str(idaa))
    print(idaa)

    fieldlist = ['id', 'test_case_id', 'name', 'status', 'steps', 'runtime', 'message', 'screenshot', 'test_suite_id', 'ip', 'browser_type']
    rows = 1000
    if len(fieldlist) == 0:
        fieldlist = ['id', 'test_case_id', 'name', 'status', 'steps', 'runtime', 'message', 'screenshot',
                     'test_suite_id', 'ip', 'browser_type']
    search_value = fieldlist[0]
    for i in range(1, len(fieldlist)):
        search_value = search_value + ',' + fieldlist[i]
    condition = ''
    search_type1 = ''
    search_type2 = ''
    for i in range(len(conditionList)):
        if str(conditionList[i]) == 'id':
            search_type1 = '="'
            search_type2 = '"'
        else:
            search_type1 = ' like "%'
            search_type2 = '%"'
        if i == 0:
            condition += str(conditionList[i]) + search_type1 + str(valueList[i]) + search_type2
            # condition += str(conditionList[i]) + ' like "%' + str(valueList[i]) + '%"'
        else:
            condition += ' and ' + str(conditionList[i]) + search_type1 + str(valueList[i]) + search_type2
            # condition += ' and ' + str(conditionList[i]) + ' like "%' + str(valueList[i]) + '%"'
    results = []
    sql = 'select ' + search_value + ' from test_batch where ' + str(condition) + ' order by id desc limit ' + str(
        rows) + ';'
    cases = useDB.useDB().search(sql)
    log.log().logger.info('cases : %s ' % cases)
    for i in range(len(cases)):
        result = {}
        for j in range(len(fieldlist)):
            if fieldlist[j] == 'runtime' and cases[i][j] is not None:
                result0 = cases[i][j].strftime('%Y-%m-%d %H:%M:%S')
            elif fieldlist[j] == 'status':
                if cases[i][3] == 0:
                    result0 = '0-待执行'
                elif cases[i][3] == 1:
                    result0 = '1-执行成功'
                elif cases[i][3] == 4:
                    result0 = '4-执行中'
                elif cases[i][3] == 2:
                    result0 = '2-执行失败'
                elif cases[i][3] == 3:
                    result0 = '3-无法执行'
                elif cases[i][3] == -1:
                    result0 = '-1-默认'
                else:
                    result0 = cases[i][3]
            else:
                result0 = cases[i][j]
            result[fieldlist[j]] = result0
        results.append(result)
    log.log().logger.info("results is :%s" % results)
    print("测试案例详情", results)
    # print(pd.DataFrame(list(results)))

    return export_excel(fieldlist,results)

def export_excel(order,export):
    pf = pd.DataFrame(list(export))
    pf = pf[order]
    ltime = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime(time.time()))
    print(ltime)
    path1 = os.getcwd()
    path2 = path1 + '/app/static/out'
    print(path2)
    path3 = path2 + '/out-' + ltime + '.xlsx'
    path4 = 'static/out' + '/out-' + ltime + '.xlsx'
    if Path(path2).exists() == 0:
        os.makedirs(path2)
    print(path3)
    file_path = pd.ExcelWriter(path3)
    #替换空单元格
    # pf.fillna(' ',inplace = True)
    pf.to_excel(file_path,encoding = 'utf-8',index = False)
    file_path.save()
    return "/" + path4